package cn.hfatec.shms.web.yk.ykOrder.service;

import cn.hfatec.JsonResponse;
import cn.hfatec.Page;
import cn.hfatec.Table;
import cn.hfatec.base.BaseDao;
import cn.hfatec.shms.advice.annotation.MysqlDataSource;
import cn.hfatec.shms.advice.annotation.OracleDataSource;
import cn.hfatec.shms.cache.CodeInfoCache;
import cn.hfatec.shms.constants.ShmsConstant;
import cn.hfatec.shms.constants.ShmsProperties;
import cn.hfatec.shms.enums.CodeType;
import cn.hfatec.shms.enums.KeyTypeEnum;
import cn.hfatec.shms.exception.KeyGenException;
import cn.hfatec.shms.exception.NotAuthException;
import cn.hfatec.shms.system.codeInfo.model.CodeInfo;
import cn.hfatec.shms.system.organization.model.Organization;
import cn.hfatec.shms.system.organization.service.OrganizationService;
import cn.hfatec.shms.system.user.model.User;
import cn.hfatec.shms.utils.ExcelUtil;
import cn.hfatec.shms.utils.KeyGenUtils;
import cn.hfatec.shms.web.yk.ykGather.dao.YkGatherDao;
import cn.hfatec.shms.web.yk.ykGather.model.YkGather;
import cn.hfatec.shms.web.yk.ykGather.service.YkGatherService;
import cn.hfatec.shms.web.yk.ykOrder.dao.YkOrderDao;
import cn.hfatec.shms.web.yk.ykOrder.model.YkOrder;
import cn.hfatec.shms.web.yk.ykOrder.model.YkOrderCheck;
import cn.hfatec.shms.web.yk.ykOrder.model.YkOrderParams;
import cn.hfatec.shms.web.yk.ykType.model.YkType;
import cn.hfatec.shms.web.yk.ykType.service.YkTypeService;
import cn.hfatec.shms.web.yk.ykUser.dao.YkUserDao;
import cn.hfatec.shms.web.yk.ykUser.model.YkUser;
import cn.hfatec.shms.web.yk.ykUser.service.YkUserService;
import com.google.common.base.Optional;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import static cn.hfatec.shms.web.WeekTime.time;


/**
 * @author hfatec
 * @version 1.0
 *          <p/>
 *          the JAVA code is generate by middlegen
 */
@Service("YkOrderService")
public class YkOrderService {

    public static final String BEAN_ID = "YkOrderService";

    //注入Dao层
    @Resource(name = "YkOrderDao")
    private YkOrderDao ykOrderDao;

    //注入BaseDao
    @Resource(name = "BaseDao")
    private BaseDao baseDao;
    //注入Dao层
    @Resource(name = "YkGatherDao")
    private YkGatherDao ykGatherDao;
    //注入Dao层
    @Resource(name = "YkUserDao")
    private YkUserDao ykUserDao;
    //服务层
    @Resource(name = "YkUserService")
    private YkUserService ykUserService;
    //服务层
    @Resource(name = "OrganizationService")
    private OrganizationService organizationService;

    @Resource(name = "shmsProperties")
    private ShmsProperties shmsProperties;
    //服务层
    @Resource(name = "YkTypeService")
    private YkTypeService ykTypeService;
    //服务层
    @Resource(name = "YkGatherService")
    private YkGatherService ykGatherService;

    @Transactional
    public static String encodeFilename(HttpServletRequest request, String fileName) {
        String rtn = "";
        try {
            String userAgent = request.getHeader("USER-AGENT");
            String new_filename = URLEncoder.encode(fileName, "UTF8");

            rtn = "filename=\"" + new_filename + "\"";

            if (userAgent != null) {
                userAgent = userAgent.toLowerCase();

                if (userAgent.indexOf("msie") != -1) {
                    rtn = "filename=\"" + new_filename + "\"";
                } else if (userAgent.indexOf("opera") != -1) {
                    rtn = "filename*=UTF-8''" + new_filename;
                } else if (userAgent.indexOf("safari") != -1) {
                    rtn = "filename=\"" + new String(fileName.getBytes("UTF-8"), "ISO8859-1") + "\"";
                } else if (userAgent.indexOf("applewebkit") != -1) {
                    new_filename = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
                    rtn = "filename=\"" + new_filename + "\"";
                } else if (userAgent.indexOf("mozilla") != -1) {
                    rtn = "filename*=UTF-8''" + new_filename;
                }
            }
        } catch (UnsupportedEncodingException e) {
            try {
                rtn = new String(fileName.getBytes("UTF-8"), "iso-8859-1");
            } catch (UnsupportedEncodingException localUnsupportedEncodingException1) {
            }
        }
        return rtn;
    }

    /**
     * 查询数据
     *
     * @param ykOrderParams
     * @param page
     */
    @Transactional
    @MysqlDataSource
    public Table queryYkOrderTable(YkOrderParams ykOrderParams, Page page) {

        HashMap<String, Object> params = new HashMap<String, Object>();
        params.put("params", ykOrderParams);
        return baseDao.queryTableByPage("queryYkOrder", params, page);
    }

    /**
     * 查询数据
     *
     * @param ykOrderParams
     */
    @Transactional
    @MysqlDataSource
    public YkOrder hjYkOrder(YkOrderParams ykOrderParams) {
        return ykOrderDao.hjYkOrder(ykOrderParams);
    }

    /**
     * 插入数据
     *
     * @param ykOrder
     */
    @Transactional
    @MysqlDataSource
    public void saveYkOrder(YkOrder ykOrder, String[] ids, String[] gatherType, String[] gatherMoney, User user) throws KeyGenException {
        YkGather ykGather = new YkGather();
        YkUser ykUser = new YkUser();
        if (ykOrder.getId() != null) {
            YkOrder result = getYkOrder(ykOrder.getId());
            if (result != null) {
                ykOrder.setSpareF(time());
                ykOrder.setSpareG(user.getLoginName());
                ykOrderDao.update(ykOrder);

                if (gatherType != null) {
                    for (int i = 0; i < gatherType.length; i++) {
                        ykGather.setBillId(result.getBillId());
                        if (!gatherType[i].equals(""))
                            ykGather.setGatherType(gatherType[i]);

                        if (gatherMoney.length != 0) {
                            if (!gatherMoney[i].equals(""))
                                ykGather.setGatherMoney(gatherMoney[i]);
                            else
                                ykGather.setGatherMoney("0");
                        } else
                            ykGather.setGatherMoney("0");

                        if (ids.length > 0 && ids[i] != "") {
                            ykGather.setId(Integer.parseInt(ids[i]));
                            ykGatherDao.update(ykGather);
                        } else {

                            ykGather.setId(null);
                            ykGatherDao.insert(ykGather);
                        }
                    }
                }
            } else {
                ykOrder.setBillId(KeyGenUtils.createKeyByType(KeyTypeEnum.BILL_KEY));
                ykOrder.setCreateTime(new Date());
                ykOrder.setCreateUser(user.getLoginName());

                if (ykOrder.getSpareA().equals("0") || ykOrder.getSpareA() == null) {
                    ykUser.setUserId(KeyGenUtils.createKeyByType(KeyTypeEnum.USER_KEY));
                    ykOrder.setUserId(ykUser.getUserId());
                    ykUser.setStaffId(ykOrder.getStaffId());
                    ykUser.setCardId(ykOrder.getCardId());
                    ykUser.setUserName(ykOrder.getCustomerName());
                    ykUser.setAge(ykOrder.getAge());
                    ykUser.setSex(ykOrder.getSex());
                    ykUser.setAddress(ykOrder.getAddress());
                    ykUser.setPhone(ykOrder.getPhone());
                    ykUserService.saveYkUser(ykUser, user);
                }
                ykOrderDao.insert(ykOrder);
                if (gatherType != null) {
                    for (int i = 0; i < gatherType.length; i++) {
                        ykGather.setBillId(ykOrder.getBillId());
                        if (!gatherType[i].equals(""))
                            ykGather.setGatherType(gatherType[i]);

                        if (gatherMoney.length != 0) {
                            if (!gatherMoney[i].equals(""))
                                ykGather.setGatherMoney(gatherMoney[i]);
                            else
                                ykGather.setGatherMoney("0");
                        } else
                            ykGather.setGatherMoney("0");
                        ykGatherDao.insert(ykGather);
                    }
                }
            }
        } else {
            ykOrder.setBillId(KeyGenUtils.createKeyByType(KeyTypeEnum.BILL_KEY));
            ykOrder.setCreateTime(new Date());
            ykOrder.setCreateUser(user.getLoginName());

            if (ykOrder.getSpareA().equals("0") || ykOrder.getSpareA() == null) {
                ykUser.setUserId(KeyGenUtils.createKeyByType(KeyTypeEnum.USER_KEY));
                ykOrder.setUserId(ykUser.getUserId());
                ykUser.setStaffId(ykOrder.getStaffId());
                ykUser.setCardId(ykOrder.getCardId());
                ykUser.setUserName(ykOrder.getCustomerName());
                ykUser.setAge(ykOrder.getAge());
                ykUser.setSex(ykOrder.getSex());
                ykUser.setAddress(ykOrder.getAddress());
                ykUser.setPhone(ykOrder.getPhone());
                ykUserService.saveYkUser(ykUser, user);
            }
            ykOrderDao.insert(ykOrder);
            if (gatherType != null) {
                for (int i = 0; i < gatherType.length; i++) {
                    ykGather.setBillId(ykOrder.getBillId());
                    if (!gatherType[i].equals(""))
                        ykGather.setGatherType(gatherType[i]);

                    if (gatherMoney.length != 0) {
                        if (!gatherMoney[i].equals(""))
                            ykGather.setGatherMoney(gatherMoney[i]);
                        else
                            ykGather.setGatherMoney("0");
                    } else
                        ykGather.setGatherMoney("0");
                    ykGatherDao.insert(ykGather);
                }
            }
        }
    }

    /**
     * 导入插入数据
     *
     * @param ykOrder
     */
    @Transactional
    @MysqlDataSource
    public void saveYkOrderDaoru(YkOrder ykOrder, User user) throws KeyGenException {
        YkGather ykGather = new YkGather();
        YkUser ykUser = new YkUser();
        if (ykOrder.getId() != null) {
            YkOrder result = getYkOrder(ykOrder.getId());
            if (result != null) {
                ykOrder.setSpareF(String.valueOf(new Date()));
                ykOrder.setSpareG(user.getLoginName());
                ykOrderDao.update(ykOrder);

            } else {
                ykOrder.setBillId(KeyGenUtils.createKeyByType(KeyTypeEnum.BILL_KEY));
                ykOrder.setCreateTime(new Date());
                ykOrder.setCreateUser(user.getLoginName());

                if (ykOrder.getSpareA().equals("0") || ykOrder.getSpareA() == null) {
                    ykUser.setUserId(KeyGenUtils.createKeyByType(KeyTypeEnum.USER_KEY));
                    ykOrder.setUserId(ykUser.getUserId());
                    ykUser.setStaffId(ykOrder.getStaffId());
                    ykUser.setCardId(ykOrder.getCardId());
                    ykUser.setUserName(ykOrder.getCustomerName());
                    ykUser.setAge(ykOrder.getAge());
                    ykUser.setSex(ykOrder.getSex());
                    ykUser.setAddress(ykOrder.getAddress());
                    ykUser.setPhone(ykOrder.getPhone());
                    ykUserService.saveYkUser(ykUser, user);
                }

                String[] gatherTypes = null;
                if (ykOrder.getGatherType() != null) {
                    String payment = ykOrder.getGatherType().replace("，", ",") + ",";
                    if (StringUtils.isNotBlank(payment)) {
                        if (payment.contains(",")) {
                            gatherTypes = payment.split(",");
                        }
                    }
                }
                String[] gatherMoneys = null;
                if (ykOrder.getGatherMoney() != null) {
                    String sprice = ykOrder.getGatherMoney().replace("，", ",") + ",";
                    if (StringUtils.isNotBlank(sprice)) {
                        if (sprice.contains(",")) {
                            gatherMoneys = sprice.split(",");
                        }
                    }
                }
                ykGather = new YkGather();
                for (int i = 0; i < gatherTypes.length; i++) {
                    ykGather.setBillId(ykOrder.getBillId());
                    ykGather.setGatherType(gatherTypes[i]);
                    ykGather.setGatherMoney(gatherMoneys[i]);
                    ykGatherService.saveYkGather(ykGather);
                }
                ykOrderDao.insert(ykOrder);

            }
        } else {
            ykOrder.setBillId(KeyGenUtils.createKeyByType(KeyTypeEnum.BILL_KEY));
            ykOrder.setCreateTime(new Date());
            ykOrder.setCreateUser(user.getLoginName());

            if (ykOrder.getSpareA().equals("0") || ykOrder.getSpareA() == null) {
                ykUser.setUserId(KeyGenUtils.createKeyByType(KeyTypeEnum.USER_KEY));
                ykOrder.setUserId(ykUser.getUserId());
                ykUser.setStaffId(ykOrder.getStaffId());
                ykUser.setCardId(ykOrder.getCardId());
                ykUser.setUserName(ykOrder.getCustomerName());
                ykUser.setAge(ykOrder.getAge());
                ykUser.setSex(ykOrder.getSex());
                ykUser.setAddress(ykOrder.getAddress());
                ykUser.setPhone(ykOrder.getPhone());
                ykUserService.saveYkUser(ykUser, user);
            }


            String[] gatherTypes = null;
            if (ykOrder.getGatherType() != null) {
                String payment = ykOrder.getGatherType().replace("，", ",") + ",";
                if (StringUtils.isNotBlank(payment)) {
                    if (payment.contains(",")) {
                        gatherTypes = payment.split(",");
                    }
                }
            }
            String[] gatherMoneys = null;
            if (ykOrder.getGatherMoney() != null) {
                String sprice = ykOrder.getGatherMoney().replace("，", ",") + ",";
                if (StringUtils.isNotBlank(sprice)) {
                    if (sprice.contains(",")) {
                        gatherMoneys = sprice.split(",");
                    }
                }
            }
            ykGather = new YkGather();
            for (int i = 0; i < gatherTypes.length; i++) {
                ykGather.setBillId(ykOrder.getBillId());
                ykGather.setGatherType(gatherTypes[i]);
                ykGather.setGatherMoney(gatherMoneys[i]);
                ykGatherService.saveYkGather(ykGather);
            }
            ykOrderDao.insert(ykOrder);

        }
    }

    /**
     * 删除数据
     *
     * @param id
     */
    @Transactional
    @MysqlDataSource
    public void deleteYkOrder(Integer id) {
        YkOrder ykOrder = getYkType(id);
        if (ykOrder != null) {
            ykGatherService.deleteYkGather(ykOrder.getBillId());
        }
        ykOrderDao.delete(id);
    }

    /**
     * 删除数据
     *
     * @param ykOrderList
     */
    @Transactional
    @MysqlDataSource
    public void deleteYkOrderBatch(List<String> ykOrderList) {
        for (String id : ykOrderList) {
            deleteYkOrder(Integer.valueOf(id));
        }
    }

    /**
     * 根据ID获取数据
     *
     * @param id
     */
    @Transactional
    @MysqlDataSource
    public YkOrder getYkOrder(Integer id) {

        return ykOrderDao.getYkOrderById(id);
    }

    /**
     * 根据ID获取数据
     *
     * @param staffId
     */
    @Transactional
    @MysqlDataSource
    public YkOrder getYkOrderstaffId(String staffId) {

        return ykOrderDao.getYkOrderBystaffId(staffId);
    }

    /**
     * 根据ID获取数据
     *
     * @param id
     */
    @Transactional
    @MysqlDataSource
    public YkOrder getYkType(Integer id) {

        return ykOrderDao.getYkTypeById(id);
    }

    /**
     * 根据ID获取数据
     *
     * @param staffId
     */
    @Transactional
    @OracleDataSource
    public YkOrder getXh(String staffId) {

        return ykOrderDao.getXhByStaffId(staffId);
    }

    /**
     * 导入健康管理数据
     *
     * @param path
     * @param request
     * @param response
     * @return
     * @throws java.io.IOException
     */
    @Transactional
    @MysqlDataSource
    public String importCust(String path, HttpServletRequest request, HttpServletResponse response) throws Exception {
        String filePath = path.substring(path.indexOf("/upload") + 7);
        User user = (User) request.getSession().getAttribute(ShmsConstant.LOGIN_USER_SESSION_NAME);
        Organization organization = new Organization();
        if (user == null) {
            throw new NotAuthException("用户未授权");
        } else {
            organization = organizationService.getOrganizationByOrgId(user.getOrgId());
        }
        String uploadPath = shmsProperties.getUploadPath();
        File file = new File(uploadPath + File.separator + filePath);
        boolean flag = false;
        String rule = "^1[358]\\d{9}$";
        String errorMsg = "";
        Workbook workbook = null;
        workbook = WorkbookFactory.create(file);
        Sheet sh = workbook.getSheetAt(0);//
        List<YkOrderCheck> potentialCusts = this.buildCustWithAjaxUpload(sh);//
        if (potentialCusts != null && potentialCusts.size() > 0) {
            for (YkOrderCheck ykOrderCheck : potentialCusts) {
                //判读是否为有效数据
                if (ykOrderCheck.isValid()) {
                    YkOrder ykOrder = ykOrderCheck.getYkOrder();
                    if (organization != null) {
                        ykOrder.setSpareB(organization.getOrgLevelCode());
                    }
                    Optional<JsonResponse<String>> optional = checkCustByUniqueConstraint(ykOrder);
                    if (optional.isPresent()) {
                        errorMsg += "第" + ykOrderCheck.getRowNumber() + "收款方式和收款金额个数不等,不能导入!";
                        continue;
                    }
                    YkType ykType = ykTypeService.getYkTypeProductNam(ykOrder.getProductName());
                    if (ykType == null) {
                        errorMsg += "第" + ykOrderCheck.getRowNumber() + "产品名称有误";
                        continue;
                    } else {
                        ykOrder.setVipId(ykType.getId());
                        ykOrder.setVipType(ykType.getVipType().toString());
                        ykOrder.setMoney(ykType.getPrice());
                        ykOrder.setVipCycle(ykType.getCycle().toString());
                        ykOrder.setCostType(ykType.getCostType());
                        ykOrder.setCostMoney(ykType.getCostMoney());
                        ykOrder.setCost("0");
                        ykOrder.setDoorNum(ykType.getDoorNum().toString());
                        ykOrder.setDoorMoney(ykType.getDoorMoney());
                    }

                    YkOrder ykyyh = getYkOrderstaffId(ykOrder.getStaffId());
                    if (ykyyh != null) {
                        ykOrder.setRecordNo(ykyyh.getRecordNo());
                    }
                    ykOrder.setSpareA("0");
                    saveYkOrderDaoru(ykOrder, user);
                    flag = true;
                } else {//无效数据
                    errorMsg += "第" + ykOrderCheck.getRowNumber() + "有错，不能导入!";
                }
            }
        } else {
            return new JsonResponse<String>(JsonResponse.RETURN_FAILURE, "导入的文件无符合数据").toString();
        }

        if (flag) {
            if ("".equals(errorMsg)) {
                return new JsonResponse<String>(JsonResponse.RETURN_SUCCESS, "导入成功!" + errorMsg).toString();
            }
            return new JsonResponse<String>(JsonResponse.RETURN_SUCCESS, "部分导入成功!" + errorMsg).toString();
        } else {
            return new JsonResponse<String>(JsonResponse.RETURN_SUCCESS, "导入失败!" + errorMsg).toString();
        }

    }

    public Date luDate(String strDate) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date date = null;
        try {
            date = sdf.parse(strDate);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return date;
    }

    /**
     * 读取潜在客户导入的数据
     *
     * @param sh
     * @return
     * @throws Exception
     */
    @Transactional
    @MysqlDataSource
    private List<YkOrderCheck> buildCustWithAjaxUpload(Sheet sh) throws Exception {
        List<YkOrderCheck> checks = new ArrayList<YkOrderCheck>();
        int rows = sh.getLastRowNum() + 1 - sh.getFirstRowNum(); // 总行数
        List<CodeInfo> sexList = CodeInfoCache.getCodeInfo(CodeType.SEX);
        List<CodeInfo> vipList = CodeInfoCache.getCodeInfo(CodeType.VIP_TYPE);
        List<CodeInfo> jkglList = CodeInfoCache.getCodeInfo(CodeType.JKGL_TYPE);
        List<CodeInfo> channelList = CodeInfoCache.getCodeInfo(CodeType.CHANNEL_TYPE);
        List<CodeInfo> vipqdList = CodeInfoCache.getCodeInfo(CodeType.VIPQD_TYPE);
        ExcelUtil excelUtil = new ExcelUtil();
        for (int i = 1; i < rows; i++) {
            boolean flag = true;//数据是否有效(客户姓名，手机号存在)
            boolean flag2 = false;//判断是否该行有数据
            YkOrder ykOrder = new YkOrder();
            YkOrderCheck ykOrderCheck = new YkOrderCheck();
            ykOrderCheck.setRowNumber(i + 1);
            ykOrderCheck.setValid(true);
            String str = "";//下拉框
            // HSSFRow row = sh.getRow(i);//excel97-2003写法
            Row row = sh.getRow(i);
//            int cols = row.getLastCellNum() + 1 - row.getFirstCellNum(); // 该行的总列数
            Iterator cells = row.cellIterator();
            int j = 0;
            while (cells.hasNext()) {
                //判断行数据的客户姓名和电话是否为空，某一项为空则不执行循环

//                HSSFCell cell = (HSSFCell) cells.next();//excel97-2003写法
                XSSFCell cell = (XSSFCell) cells.next();

                if (j == 0) {
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        ykOrder.setStaffId(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 1) {
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        ykOrder.setCardId(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 2) {

                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        ykOrder.setCustomerName(ExcelUtil.getStringRowCellValue(row, j));
                    }

                } else if (j == 3) {

                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        ykOrder.setPhone(ExcelUtil.getStringRowCellValue(row, j));
                    }

                } else if (j == 4) {

                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        ykOrder.setAge(ExcelUtil.getStringRowCellValue(row, j));
                    }

                } else if (j == 5) {

                    str = excelUtil.getStringRowCellValue(row, j);
                    if (!StringUtils.isEmpty(str)) {
                        for (CodeInfo c : sexList) {
                            if (c.getContent().equals(str)) {
                                ykOrder.setSex(c.getValue());
                            }
                        }
                    }

                } else if (j == 6) {
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        ykOrder.setAddress(ExcelUtil.getStringRowCellValue(row, j));
                    }// 性别
                } else if (j == 7) {

                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        ykOrder.setProductName(excelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 8) {
                    str = excelUtil.getStringRowCellValue(row, j);
                    if (!StringUtils.isEmpty(str)) {
                        for (CodeInfo c : channelList) {
                            if (c.getContent().equals(str)) {
                                ykOrder.setChannelType(c.getValue());
                            }
                        }
                    }
                } else if (j == 9) {
                    str = excelUtil.getStringRowCellValue(row, j);
                    if (!StringUtils.isEmpty(str)) {
                        for (CodeInfo c : vipqdList) {
                            if (c.getContent().equals(str)) {
                                ykOrder.setSpareC(c.getValue());
                            }
                        }
                    }
                } else if (j == 10) {

                    if (!StringUtils.isEmpty(excelUtil.getRowCellValueDate(row, j).toString())) {
                        ykOrder.setBillTime(stringDate(excelUtil.getRowCellValueDate(row, j)));
                    }
                } else if (j == 11) {

                    if (!StringUtils.isEmpty(excelUtil.getRowCellValueDate(row, j).toString())) {
                        ykOrder.setBillStart(stringDate(excelUtil.getRowCellValueDate(row, j)));
                    }
                } else if (j == 12) {

                    if (!StringUtils.isEmpty(excelUtil.getRowCellValueDate(row, j).toString())) {
                        ykOrder.setBillEnd(stringDate(excelUtil.getRowCellValueDate(row, j)));
                    }
                } else if (j == 13) {

                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        ykOrder.setYjName(excelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 14) {

                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        ykOrder.setChName(excelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 15) {

                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        ykOrder.setFgName(excelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 16) {

                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        ykOrder.setVipFh(excelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 17) {

                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        ykOrder.setGatherType(excelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 18) {

                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        ykOrder.setGatherMoney(excelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 19) {

                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        ykOrder.setNote(excelUtil.getStringRowCellValue(row, j));
                    }
                }
                j++;
            }
            if (flag) {
//                 list.add(cust);
                ykOrderCheck.setYkOrder(ykOrder);
                checks.add(ykOrderCheck);
            } else {
//                TdCheck.setCustom(cust);
                if (flag2) {
                    checks.add(ykOrderCheck);
                }
            }
        }
        return checks;
    }

    public String stringDate(Date billTime) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String sDate = sdf.format(billTime);
        return sDate;
    }

    /**
     * 根据客户姓名和手机查询唯一性
     *
     * @param ykOrder
     * @return
     */
    @Transactional
    public Optional checkCustByUniqueConstraint(YkOrder ykOrder) {
        String payment = ykOrder.getGatherType().replace("，", ",");
        String sprice = ykOrder.getGatherMoney().replace("，", ",");
        int l = lenghtNum(payment);
        int s = lenghtNum(sprice);
        if (l != s) {
            return Optional.of(new JsonResponse<String>(JsonResponse.RETURN_FAILURE, "收款方式和收款金额个数不等!"));
        }
        return Optional.absent();
    }

    public int lenghtNum(String lenghts) {
        int a = lenghts.length();
        String b = lenghts.replace(",", "");
        int c = b.length();
        return a - c;
    }

    /**
     * 导出
     *
     * @param
     */
    @Transactional
    public String exportExcel(YkOrderParams ykOrderParams, HttpServletRequest request, HttpServletResponse response) {
        try {
            String clientFileName = encodeFilename(request, System.currentTimeMillis() + "");
            response.reset();
            response.setHeader("Content-Disposition", "attachment; " + clientFileName + ".xls");
            response.setContentType("application/octet-stream;charset=UTF-8");
            OutputStream os = response.getOutputStream();
            List dataList = ykOrderDao.queryYkOrder(ykOrderParams);
            List nameList = new ArrayList();
            nameList.add("订单编号");
            nameList.add("客户编号");
            nameList.add("卡号");
            nameList.add("客户姓名");
            nameList.add("会员类型");
            nameList.add("产品名称");
            nameList.add("金额");
            nameList.add("渠道类型");
            nameList.add("渠道来源");
            nameList.add("下单日期");
            nameList.add("开卡日期");
            nameList.add("到期日期");
            nameList.add("引荐人");
            nameList.add("促会人");
            nameList.add("分管人");
            nameList.add("收款方式");
            nameList.add("收款金额");
            nameList.add("成本");
            nameList.add("退会");
            nameList.add("数量");
            nameList.add("副会");
            this.exportExcel(nameList, dataList, os);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return "";
    }

    /**
     * @return HSSFWorkbook
     */
    public HSSFWorkbook createWorkbook(List fieldName, List fieldData) {
        List<CodeInfo> sexList = CodeInfoCache.getCodeInfo(CodeType.SEX);
        List<CodeInfo> vipList = CodeInfoCache.getCodeInfo(CodeType.VIP_TYPE);
        List<CodeInfo> jkglList = CodeInfoCache.getCodeInfo(CodeType.JKGL_TYPE);
        List<CodeInfo> channelList = CodeInfoCache.getCodeInfo(CodeType.CHANNEL_TYPE);
        List<CodeInfo> vipBackList = CodeInfoCache.getCodeInfo(CodeType.YES_OR_NO);
        List<CodeInfo> vipqdList = CodeInfoCache.getCodeInfo(CodeType.VIPQD_TYPE);

        // 第一步，创建一个webbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet("会员管理表");
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow((int) 0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        HSSFCellStyle cellstyle = (HSSFCellStyle) wb.createCellStyle();// 设置表头样式
        cellstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 设置垂直居中
        cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置水平居中
        cellstyle.setWrapText(true); // 设置为自动换行
        HSSFFont cellFont = (HSSFFont) wb.createFont(); // 创建表头字体样式
        cellFont.setFontHeightInPoints((short) 10); // 设置字体大小
        cellFont.setFontName("宋体"); // 设置字体类型
        cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
        cellstyle.setFont(cellFont); // 为表头样式设置字体样式


        for (int i = 0; i < fieldName.size(); i++) {
            HSSFCell cell = row.createCell((short) i);
            cell.setCellValue((String) fieldName.get(i));
            cell.setCellStyle(cellstyle);
        }

        YkOrder ykOrder = null;

        // 第五步，写入实体数据 实际应用中这些数据从数据库得到，

        for (int i = 0; i < fieldData.size(); i++) {
            row = sheet.createRow(i + 1);
            ykOrder = (YkOrder) fieldData.get(i);
            // 第四步，创建单元格，并设置值

            if (ykOrder.getBillId() == null) {
                row.createCell((short) 0).setCellValue("");
            } else {
                row.createCell((short) 0).setCellValue(ykOrder.getBillId());
            }
            if (ykOrder.getUserId() == null) {
                row.createCell((short) 1).setCellValue("");
            } else {
                row.createCell((short) 1).setCellValue(ykOrder.getUserId());
            }
            if (ykOrder.getCardId() == null) {
                row.createCell((short) 2).setCellValue("");
            } else {
                row.createCell((short) 2).setCellValue(ykOrder.getCardId());
            }
            if (ykOrder.getCustomerName() == null) {
                row.createCell((short) 3).setCellValue("");
            } else {
                row.createCell((short) 3).setCellValue(ykOrder.getCustomerName());
            }
            if (ykOrder.getVipType() == null) {
                row.createCell((short) 4).setCellValue("");
            } else {
                for (CodeInfo c : vipList) {//汉化性别信息
                    if (c.getValue().equals(ykOrder.getVipType() + "")) {
                        row.createCell((short) 4).setCellValue(c.getContent());
                        break;
                    }
                }

            }
            if (ykOrder.getProductName() == null) {
                row.createCell((short) 5).setCellValue("");
            } else {
                row.createCell((short) 5).setCellValue(ykOrder.getProductName());
            }
            if (ykOrder.getMoney() == null) {
                row.createCell((short) 6).setCellValue("");
            } else {
                row.createCell((short) 6).setCellValue(ykOrder.getMoney());
            }
            if (ykOrder.getChannelType() == null) {
                row.createCell((short) 7).setCellValue("");
            } else {
                for (CodeInfo c : channelList) {//汉化性别信息
                    if (c.getValue().equals(ykOrder.getChannelType() + "")) {
                        row.createCell((short) 7).setCellValue(c.getContent());
                        break;
                    }
                }

            }
            if (ykOrder.getSpareC() == null) {
                row.createCell((short) 8).setCellValue("");
            } else {
                for (CodeInfo c : vipqdList) {//汉化性别信息
                    if (c.getValue().equals(ykOrder.getSpareC() + "")) {
                        row.createCell((short) 8).setCellValue(c.getContent());
                        break;
                    }
                }

            }

            if (ykOrder.getBillTime() == null) {
                row.createCell((short) 9).setCellValue("");
            } else {
                row.createCell((short) 9).setCellValue(ykOrder.getBillTime());
            }
            if (ykOrder.getBillStart() == null) {
                row.createCell((short) 10).setCellValue("");
            } else {
                row.createCell((short) 10).setCellValue(ykOrder.getBillStart());
            }
            if (ykOrder.getBillEnd() == null) {
                row.createCell((short) 11).setCellValue("");
            } else {
                row.createCell((short) 11).setCellValue(ykOrder.getBillEnd());
            }
            if (ykOrder.getYjName() == null) {
                row.createCell((short) 12).setCellValue("");
            } else {
                row.createCell((short) 12).setCellValue(ykOrder.getYjName());
            }
            if (ykOrder.getChName() == null) {
                row.createCell((short) 13).setCellValue("");
            } else {
                row.createCell((short) 13).setCellValue(ykOrder.getChName());
            }
            if (ykOrder.getFgName() == null) {
                row.createCell((short) 14).setCellValue("");
            } else {
                row.createCell((short) 14).setCellValue(ykOrder.getFgName());
            }
            if (ykOrder.getGatherType() == null) {
                row.createCell((short) 15).setCellValue("");
            } else {
                row.createCell((short) 15).setCellValue(ykOrder.getGatherType());
            }
            if (ykOrder.getGatherMoney() == null) {
                row.createCell((short) 16).setCellValue("");
            } else {
                row.createCell((short) 16).setCellValue(ykOrder.getGatherMoney());
            }
            if (ykOrder.getCost() == null) {
                row.createCell((short) 17).setCellValue("");
            } else {
                row.createCell((short) 17).setCellValue(ykOrder.getCost());
            }
            if (ykOrder.getVipBack() == null) {
                row.createCell((short) 18).setCellValue("");
            } else {
                for (CodeInfo c : vipBackList) {//汉化性别信息
                    if (c.getValue().equals(ykOrder.getVipBack() + "")) {
                        row.createCell((short) 18).setCellValue(c.getContent());
                        break;
                    }
                }
                ;
            }
            if (ykOrder.getNote() == null) {
                row.createCell((short) 19).setCellValue("");
            } else {
                row.createCell((short) 19).setCellValue(ykOrder.getNote());
            }
            if (ykOrder.getVipFh() == null) {
                row.createCell((short) 20).setCellValue("");
            } else {
                row.createCell((short) 20).setCellValue(ykOrder.getVipFh());
            }

        }
        //自适应列宽
        for (int i = 0; i < fieldName.size(); i++) {
            sheet.autoSizeColumn((short) i);
        }


        return wb;
    }


    //将信息写入输出流的方法。
    public void exportExcel(List fieldName, List fieldData, OutputStream os) throws Exception {
        HSSFWorkbook wb = createWorkbook(fieldName, fieldData);
        wb.write(os);
        os.close();
    }
}